#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CY.GFive.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace CY.GFive.DALProviders.SqlServerProvider
{
	public class TeacherEducationProvider : CY.GFive.Core.Providers.DALProvider.ITeacherEducationProvider
	{
		#region Sql
		private static readonly string SqlSelectTeacherEducation = "SELECT [Id], [TeacherCode],[EducationSchool],[StartDate],[EndDate],[ProfessName],[EducationType] FROM [TeacherEducation] WHERE  [Id]=@Id";

		private static readonly string SqlInsertTeacherEducation = "INSERT INTO [TeacherEducation]([TeacherCode],[EducationSchool],[StartDate],[EndDate],[ProfessName],[EducationType]) values(@TeacherCode,@EducationSchool,@StartDate,@EndDate,@ProfessName,@EducationType);SELECT SCOPE_IDENTITY();";

		private static readonly string SqlUpdateTeacherEducation = "UPDATE [TeacherEducation] SET [TeacherCode] = @TeacherCode,[EducationSchool] = @EducationSchool,[StartDate] = @StartDate,[EndDate] = @EndDate,[ProfessName] = @ProfessName,[EducationType] = @EducationType WHERE  [Id]=@Id";

		private static readonly string SqlDeleteTeacherEducation = "DELETE [TeacherEducation] WHERE  [Id]=@Id";

		private static readonly string SqlGetAllTeacherEducation = "SELECT [Id], [TeacherCode],[EducationSchool],[StartDate],[EndDate],[ProfessName],[EducationType] FROM [TeacherEducation]";
		#endregion

		#region ITeacherEducationProvider Members
		public Core.Business.TeacherEducation Select(int id)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, id);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectTeacherEducation);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				Core.Business.TeacherEducation teacherEducation = new Core.Business.TeacherEducation();

				if (!reader.IsDBNull(0)) teacherEducation.Id = reader.GetInt32(0);
				if (!reader.IsDBNull(1)) teacherEducation.TeacherCode = reader.GetString(1);
				if (!reader.IsDBNull(2)) teacherEducation.EducationSchool = reader.GetString(2);
				if (!reader.IsDBNull(3)) teacherEducation.StartDate = reader.GetDateTime(3);
				if (!reader.IsDBNull(4)) teacherEducation.EndDate = reader.GetDateTime(4);
				if (!reader.IsDBNull(5)) teacherEducation.ProfessName = reader.GetString(5);
				if (!reader.IsDBNull(6)) teacherEducation.EducationType = reader.GetString(6);

				return teacherEducation;
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();

				return null;
			}
		}

		public void Insert(Core.Business.TeacherEducation teacherEducation)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@TeacherCode", SqlDbType.NVarChar, teacherEducation.TeacherCode);
			sql.AddParameter("@EducationSchool", SqlDbType.NVarChar, teacherEducation.EducationSchool);
			sql.AddParameter("@StartDate", SqlDbType.DateTime, teacherEducation.StartDate);
			sql.AddParameter("@EndDate", SqlDbType.DateTime, teacherEducation.EndDate);
			sql.AddParameter("@ProfessName", SqlDbType.NVarChar, teacherEducation.ProfessName);
			sql.AddParameter("@EducationType", SqlDbType.NVarChar, teacherEducation.EducationType);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertTeacherEducation);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				if (!reader.IsDBNull(0)) teacherEducation.Id = int.Parse(reader.GetDecimal(0).ToString());

				reader.Close();
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void Update(Core.Business.TeacherEducation teacherEducation)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@TeacherCode", SqlDbType.NVarChar, teacherEducation.TeacherCode);
			sql.AddParameter("@EducationSchool", SqlDbType.NVarChar, teacherEducation.EducationSchool);
			sql.AddParameter("@StartDate", SqlDbType.DateTime, teacherEducation.StartDate);
			sql.AddParameter("@EndDate", SqlDbType.DateTime, teacherEducation.EndDate);
			sql.AddParameter("@ProfessName", SqlDbType.NVarChar, teacherEducation.ProfessName);
			sql.AddParameter("@EducationType", SqlDbType.NVarChar, teacherEducation.EducationType);
			sql.AddParameter("@Id", SqlDbType.Int, teacherEducation.Id);
			sql.ExecuteSql(SqlUpdateTeacherEducation);
		}

		public void Delete(Core.Business.TeacherEducation teacherEducation)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, teacherEducation.Id);
			sql.ExecuteSql(SqlDeleteTeacherEducation);
		}

		public IList<Core.Business.TeacherEducation> GetAllTeacherEducation()
		{
			IList<Core.Business.TeacherEducation> teacherEducationlist = new List<Core.Business.TeacherEducation>();
			SqlServerUtility sql = new SqlServerUtility();

			SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllTeacherEducation);

			if(reader != null)
			{
				while(reader.Read())
				{
					Core.Business.TeacherEducation teacherEducation = new Core.Business.TeacherEducation();

					if (!reader.IsDBNull(0)) teacherEducation.Id = reader.GetInt32(0);
					if (!reader.IsDBNull(1)) teacherEducation.TeacherCode = reader.GetString(1);
					if (!reader.IsDBNull(2)) teacherEducation.EducationSchool = reader.GetString(2);
					if (!reader.IsDBNull(3)) teacherEducation.StartDate = reader.GetDateTime(3);
					if (!reader.IsDBNull(4)) teacherEducation.EndDate = reader.GetDateTime(4);
					if (!reader.IsDBNull(5)) teacherEducation.ProfessName = reader.GetString(5);
					if (!reader.IsDBNull(6)) teacherEducation.EducationType = reader.GetString(6);

					teacherEducation.MarkOld();
					teacherEducationlist.Add(teacherEducation);
				}
				reader.Close();
			}
			return teacherEducationlist;
		}
		#endregion
	}
}
